﻿<%@ WebHandler Language="C#" Class="ZSReciveMaterialConfirmHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using System.Text.RegularExpressions;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
/// <summary>
/// add by longb 2022-05-20
/// 收料确认
/// 
/// </summary>
public class ZSReciveMaterialConfirmHandler : IHttpHandler, IRequiresSessionState
{

    CurrentUserInfo m_UserInfo;
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    uLMSContainerIBusiness m_ContainerObj = uMESBusinessFactory.instance.GetuLMSContainerObject();
    uLMSMfgOrderIBusiness m_MfgOrderObj = uMESBusinessFactory.instance.GetuLMSMfgOrderObject();
    uLMSPartPlanIBusiness m_PartPlanObj = uMESBusinessFactory.instance.GetuLMSPartPlanObject();
    SealAndIssueBusiness m_issueData = new SealAndIssueBusiness();
    CollectERPConfirmBusines r_Mconfirm = new CollectERPConfirmBusines();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    #region 页面初始化
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "GetTaskOrderData": //查询任务单信息
                    GetTaskOrderData(context);
                    break;
                case "GetMaterialData": //查询物料信息
                    GetMaterialData(context);
                    break;
                case "ReciveMaterialCofirm": //收料确认
                    ReciveMaterialCofirm(context);
                    break;
                case "GetNotProductMaterialStoreCensus"://辅料库存查询
                    GetNotProductMaterialStoreCensus(context);
                    break;
                case "SaveNotProductMaterialStoreQty"://材料库存量设定保存
                    SaveNotProductMaterialStoreQty(context);
                    break;
                case "importNotProductMaterialStoreQty"://材料库存台账导入
                    importNotProductMaterialStoreQty(context);
                    break;   
                case"GetNotProductMaterialGiveCheckRemind"://材料送检提醒查询
                    GetNotProductMaterialGiveCheckRemind(context);
                    break;
                case"UpdateNotProductMaterialData"://更新-材料送检周期、有效期
                    UpdateNotProductMaterialData(context);
                    break;
                case "ImportSendCheckCycleVlDate"://材料送检周期、有效期导入
                    ImportSendCheckCycleVlDate(context);
                    break;
                case "GetMfgOrderData"://订单查询
                    GetMfgOrderData(context);
                    break;
                case "GetMaterialListData"://整台发动机物料（零件）信息
                    GetMaterialListData(context);
                    break; 
                case"ReciveMaterialListCofirm"://整台发动机物料（零件）--收料确认
                    ReciveMaterialListCofirm(context);
                    break;       
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    #endregion

    #region 方法
    /// <summary>
    /// 查询任务单信息
    /// </summary>
    /// <param name="context"></param>
    public void GetTaskOrderData(HttpContext context)
    {
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var Condition = new Dictionary<string, string>();
        Condition.Add("ContainerNo", dt.Rows[0]["ContainerNo"].ToString());
        Condition.Add("Machine", dt.Rows[0]["Machine"].ToString());
        Condition.Add("MfgOrdername", dt.Rows[0]["MfgOrdername"].ToString());
        Condition.Add("WorkFlowType", dt.Rows[0]["WorkFlowType"].ToString());
        Condition.Add("Status", dt.Rows[0]["Status"].ToString());
        Condition.Add("HasBom", "1");
        Condition.Add("usecompleted", dt.Rows[0]["usecompleted"].ToString());
        Condition.Add("PageSize", pageSize);
        Condition.Add("CurrentPageIndex", pageNumber);
        if (dt.Columns.Contains("QJ"))
        {
            Condition.Add("QJ", "");
        }
        uMESProcessResult result = m_issueData.GetCollectContainerNo(Condition);
        //for( var i=0;i<result.DBTable.Rows.Count;i++){
        //    var workflowid=result.DBTable.Rows[i]["workflowid"].ToString();
        //    var ContainerNo = result.DBTable.Rows[i]["ContainerNo"].ToString();
        //    var ContainerID = result.DBTable.Rows[i]["ContainerID"].ToString();
        //    //DataTable dtt = r_Mconfirm.GetFirstAssembleQtyAndConfirmqty(workflowid, ContainerNo, ContainerID);
        //    //var FirstAssembleQty = Convert.ToDouble(dtt.Rows[0]["TOTAL"].ToString());
        //    //var confirmqty = Convert.ToDouble(dtt.Rows[0]["CCOUNT"].ToString());
        //    //result.DBTable.Rows[i]["QTL"]=FirstAssembleQty==0?"0":(confirmqty/FirstAssembleQty).ToString("f2");
        //    var para = new Dictionary<string, string>();
        //    para.Add("ContainerNoList", ContainerNo);
        //    para.Add("ContainerIDList", ContainerID);
        //    DataTable dtt = r_Mconfirm.GetMaterialDataList(para);
        //    DataView dv = dtt.DefaultView;
        //    dv.RowFilter = "TOTALCOLLECTQTY>=FIRSTASSEMBLEQTY";
        //    var confirmqty = Convert.ToDouble(dv.ToTable().Rows.Count);

        //    result.DBTable.Rows[i]["QTL"] = dtt.Rows.Count == 0 ? 0 : confirmqty / dtt.Rows.Count;

        //}
        if (result.Result) result.Message = "查询成功";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 查询物料信息
    /// </summary>
    /// <param name="context"></param>
    public void GetMaterialData(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var strNoList = context.Request["strNoList"].ToString();
        var strIDList = context.Request["strIDList"].ToString();
        var Condition = new Dictionary<string, string>();
        Condition.Add("ContainerNoList", strNoList);
        Condition.Add("ContainerIDList", strIDList);
        uMESProcessResult result = new uMESProcessResult();
        result.DBTable = r_Mconfirm.GetMaterialDataList(Condition);
        if (dt.Columns.Contains("QJ"))
        {
            DataView dv = result.DBTable.DefaultView;
            dv.RowFilter = "TOTALCOLLECTQTY<>FIRSTASSEMBLEQTY";
            result.DBTable = dv.ToTable();
        }
        if (result.DBTable.Rows.Count > 0) result.Result = true;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// //收料确认
    /// </summary>
    /// <param name="context"></param>
    public void ReciveMaterialCofirm(HttpContext context)
    {
        var result = new uMESProcessResult();
        var ReciveMaterialCofirmList = GetReciveMaterialCofirmList(context);
        if (ReciveMaterialCofirmList.Count > 0)
        {
            result.Result = DatabaseAccess.DBUtility.DatabaseHelper.ExecuteDataByEntitys(ReciveMaterialCofirmList);
            //更新物料齐套率
            if (result.Result)
            {
                var strFirstSecondIssue = m_ContainerObj.CheckFirstSecondIssueNew(context.Request["CONTAINERID"].ToString());//判断是初装（1）、检装（2）
                DataTable QTdt = DatabaseAccess.DBUtility.DatabaseHelper.Query_ReturnDatTable("SELECT " + (strFirstSecondIssue == "1" ? "GETCONTAINER_QTL(e.CONTAINERID)" : "GETCONTAINER_JZQTL(e.CONTAINERID,e.containerno)") + "QTL FROM ENGINEPARTPLAN e WHERE e.CONTAINERID ='" + context.Request["CONTAINERID"].ToString() + "'");
                if (QTdt.Rows.Count > 0)
                {
                    List<ExcuteEntity> UPList = new List<ExcuteEntity>();
                    var upEntity = new ExcuteEntity("ENGINEPARTPLAN", ExcuteEntity.ExcuteTypeEnum.update);
                    List<FieldEntity> upList = new List<FieldEntity>();
                    upList.Add(new FieldEntity("MTFULLSETRATE", QTdt.Rows[0]["QTL"].ToString(), FieldEntity.FieldTypeEnum.Str));
                    upEntity.ExcuteFileds = upList;
                    List<FieldEntity> whereList = new List<FieldEntity>();
                    whereList.Add(new FieldEntity("CONTAINERID", context.Request["CONTAINERID"].ToString(), FieldEntity.FieldTypeEnum.Str));
                    upEntity.WhereFileds = whereList;
                    UPList.Add(upEntity);
                    var up = DatabaseAccess.DBUtility.DatabaseHelper.ExecuteDataByEntitys(UPList);
                }
            }
        }
        else { result.Message = "本次收料数量未有大于0的记录"; }
        if (result.Result) { result.Message = "确认成功"; }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 组装收料确认数据
    /// </summary>
    /// <param name="context"></param>
    /// <returns></returns>
    public List<ExcuteEntity> GetReciveMaterialCofirmList(HttpContext context)
    {
        List<ExcuteEntity> List = new List<ExcuteEntity>();
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var CONTAINERID = context.Request["CONTAINERID"].ToString();
        for (var i = 0; i < dt.Rows.Count; i++)
        {
            if (Convert.ToInt32(dt.Rows[i]["ChangeQTY"].ToString()) > 0)
            {
                var InsertEntity = new ExcuteEntity("collecterpmaterialconfirm", ExcuteEntity.ExcuteTypeEnum.insert);
                List<FieldEntity> InsertList = new List<FieldEntity>();
                InsertList.Add(new FieldEntity("confirmqty", dt.Rows[i]["ChangeQTY"].ToString(), FieldEntity.FieldTypeEnum.Str));
                InsertList.Add(new FieldEntity("confirmdate", DateTime.Now, FieldEntity.FieldTypeEnum.DateTime));
                InsertList.Add(new FieldEntity("confirmempoyeeid", m_UserInfo.EmployeeID, FieldEntity.FieldTypeEnum.Str));
                InsertList.Add(new FieldEntity("collecterpmaterialconfirmid", Guid.NewGuid().ToString(), FieldEntity.FieldTypeEnum.Str));
                InsertList.Add(new FieldEntity("collecterpmaterialid", dt.Rows[i]["collecterpmaterialid"].ToString(), FieldEntity.FieldTypeEnum.Str));
                InsertList.Add(new FieldEntity("CONTAINERID", CONTAINERID, FieldEntity.FieldTypeEnum.Str));
                InsertEntity.ExcuteFileds = InsertList;
                List.Add(InsertEntity);
            }
        }
        return List;
    }
    /// <summary>
    /// //辅料库存查询
    /// </summary>
    /// <param name="context"></param>
    protected void GetNotProductMaterialStoreCensus(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        var result = new uMESProcessResult();
        var para = new Dictionary<string, string>();
        para.Add("Name", dt.Rows[0]["Name"].ToString());
        para.Add("Description", dt.Rows[0]["Description"].ToString());
        para.Add("Status", dt.Rows[0]["Status"].ToString() == "1" ? "" : "建议采购,急需采购");
        para.Add("PageSize", pageSize);
        para.Add("CurrentPageIndex", pageNumber);
        result = r_Mconfirm.GetAuxMaterialSeafStore(para);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 材料库存量设定保存
    /// </summary>
    /// <param name="context"></param>
    private void SaveNotProductMaterialStoreQty(HttpContext context) 
    {
        var argPara = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        var result = new uMESProcessResult();
        var selectSQL = @"select * from AuxMaterialStock where AUXMATERIALNAME='{0}' and FACTORYID='{1}'";
        var insertSQL = @"insert into AuxMaterialStock(AUXMATERIALSTOCKID,AUXMATERIALNAME,DESCRIPTION,MINSTOCKQTY,SAFESTOCKQTY,FACTORYID,UPDATEEMPLOYEEID,UPDATEDATE) 
                        values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',sysdate)";
        var updateSQL = @"update AuxMaterialStock set MINSTOCKQTY='{0}',SAFESTOCKQTY='1',UPDATEEMPLOYEEID='{2}',UPDATEDATE=sysdate where AUXMATERIALSTOCKID='{3}'";
        DataTable dt=DatabaseHelper.Query_ReturnDatTable(String.Format(selectSQL, argPara["matNR"],m_UserInfo.FactoryID));
        var arrSQL = new ArrayList();
        if (dt.Rows.Count > 0) {
            arrSQL.Add(string.Format(updateSQL, argPara["minQty"], argPara["safeQty"], m_UserInfo.EmployeeID,dt.Rows[0]["AUXMATERIALSTOCKID"]));
        } else {
            arrSQL.Add(string.Format(insertSQL, Guid.NewGuid().ToString(), argPara["matNR"], argPara["makTX"], argPara["minQty"], argPara["safeQty"], m_UserInfo.FactoryID,m_UserInfo.EmployeeID));
        }
        result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
        result.Message = result.Result ? "设定成功" : "设定失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="context"></param>
    private void importNotProductMaterialStoreQty(HttpContext context) 
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }

            if (!System.IO.Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = System.IO.Path.GetExtension(file.FileName);
            string fileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除
            NPOI.SS.UserModel.IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            DataTable dt = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            uLMS_CommonFunction.DeleteFile(fpath);
            uMESProcessResult result = SaveData(dt);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            uLMS_CommonFunction.DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }

    private uMESProcessResult SaveData(DataTable dt)
    {
        var result = new uMESProcessResult();

        var sheetHeads = @"工厂,物料,物料描述,物料类型,物料组,库管员,库管员名称,库存地点,批次号,特殊库存,特殊库存编号,特殊库存编号2,计量单位,
                        供应商,供应商名称,客户,客户名称,可用库存,质检库存,冻结库存,在途库存,退回库存,收货冻结库存,未清采购订单数量,
                        生产日期,供应商的批次,炉批号,锭节点,状态,限制使用连台号,不合格审理单号,供应商代料单号,生产厂家,备注,限制使用机型,读取状态";
        var dbClumns = @"WERKS,MATNR,MAKTX,MTART,MATKL,ZBGY,ZBGYT,LGORT,CHARG,SOBKZ,SONUM,CHAR24,MEINS,
                        LIFNR,NAME1,KUNNR,NAME2,LABST,EINME,SPEME,UMLMC,RETME,WESBS,MENGE,
                        CHAR01,CHAR02,CHAR03,CHAR04,CHAR05,CHAR06,CHAR07,CHAR08,CHAR09,CHAR10,CHAR11,STATUS";
       
        var insertStr = "insert into T_LBJKC({0})values({1})";
        var arrSQL = new ArrayList();
        //DataTable NOdt = new DataTable();
        for (var i = 0; i < dt.Rows.Count;i++ )
        {
            //DataRow NORow = NOdt.NewRow();
            var insertClumns = string.Empty;
            var insertValues = string.Empty;
            for (var j = 0; j < sheetHeads.Split(',').Length;j++)
            {
                if (dt.Columns.Contains(sheetHeads.Split(',')[j]))
                {
                    //if (!NOdt.Columns.Contains(dbClumns.Split(',')[j])) { NOdt.Columns.Add(dbClumns.Split(',')[j]); }
                    //NORow[dbClumns.Split(',')[j]] = dt.Rows[i][sheetHeads.Split(',')[j]];
                    insertClumns += dbClumns.Split(',')[j] + ",";
                    insertValues += "'" + dt.Rows[i][sheetHeads.Split(',')[j]] + "',";
                }
            }
            //NOdt.Rows.Add(NORow);
            if (!string.IsNullOrWhiteSpace(insertClumns))
            {
                arrSQL.Add(string.Format(insertStr, insertClumns.TrimEnd(','), insertValues.TrimEnd(',')));
            }
        }
        if (arrSQL.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
            result.Message = result.Result ? "导入成功" : "导入失败";
        }
        else {
            result.Message = "导入模板数据为空，请录入数据再进行操作";
        }
        return result;
    }
    /// <summary>
    /// 材料送检提醒信息查询
    /// </summary>
    /// <param name="context"></param>
    private void GetNotProductMaterialGiveCheckRemind(HttpContext context) 
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        var result = new uMESProcessResult();
        var para = new Dictionary<string, string>();
        para.Add("Charg", dt.Rows[0]["Charg"].ToString());
        para.Add("Name", dt.Rows[0]["Name"].ToString());
        para.Add("Description", dt.Rows[0]["Description"].ToString());
        para.Add("SendStatus", dt.Rows[0]["Status"].ToString() == "1" ? "" : "按期送检,逾期未检");
        para.Add("PageSize", pageSize);
        para.Add("CurrentPageIndex", pageNumber);
        result = r_Mconfirm.GetAuxMaterialStore(para);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 更新--送检周期、有效期
    /// </summary>
    /// <param name="context"></param>
    private void UpdateNotProductMaterialData(HttpContext context) 
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var sendCheckCycle = context.Request["sendCheckCycle"].ToString();
        var vlDate = context.Request["vlDate"].ToString();
        var arrSQL = new ArrayList();
        var result = new uMESProcessResult();
        foreach(DataRow row in dt.Rows)
        {
            if(!string.IsNullOrWhiteSpace(sendCheckCycle)&&!string.IsNullOrWhiteSpace(row["charg"].ToString())){
                arrSQL.Add(string.Format("update T_LBJKC set sendCheckCycle='{0}' where charg='{1}' and matnr='{2}'",sendCheckCycle,row["charg"],row["matnr"]));
            }
            if (!string.IsNullOrWhiteSpace(vlDate) && !string.IsNullOrWhiteSpace(row["charg"].ToString()))
            {
                arrSQL.Add(string.Format("update T_LBJKC set vldat=to_date('{0}','yyyy-MM-dd') where charg='{1}' and matnr='{2}'", vlDate, row["charg"], row["matnr"]));
            }
        }
        result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
        result.Message = result.Result ? "更新成功" : "更新失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 材料--送检周期、有效期--导入
    /// </summary>
    /// <param name="context"></param>
    private void ImportSendCheckCycleVlDate(HttpContext context) 
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            if (!System.IO.Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = System.IO.Path.GetExtension(file.FileName);
            string fileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除
            NPOI.SS.UserModel.IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            DataTable dt = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            uLMS_CommonFunction.DeleteFile(fpath);
            uMESProcessResult result = SaveSendCheckCycleVlDate(dt);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            uLMS_CommonFunction.DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    private uMESProcessResult SaveSendCheckCycleVlDate(DataTable dt)
    {
        var result = new uMESProcessResult();
        if(!dt.Columns.Contains("物料编码")){
            result.Message="导入模板未包含【物料编码】列";
            return result;
        }
        if(!dt.Columns.Contains("批次号")){
            result.Message="导入模板未包含【批次号】列";
            return result;
        }
        var updateStr = "update T_LBJKC set {0} where charg='{1}' and matnr='{2}' ";
        var arrSQL = new ArrayList();
        var msg = string.Empty;
        for (var i = 0; i < dt.Rows.Count; i++)
        {
            DataTable dtt = DatabaseHelper.Query_ReturnDatTable(string.Format("select * from T_LBJKC where charg='{0}' and matnr='{1}'", dt.Rows[i]["批次号"], dt.Rows[i]["物料编码"]));
            if (dtt.Rows.Count==0) {
                msg += dt.Rows[i]["物料编码"] + "-" + dt.Rows[i]["批次号"] + ",";
                continue;
            }
             var updateClumns=string.Empty;
             var date = new DateTime();
            if (dt.Columns.Contains("有效期") && DateTime.TryParse(dt.Rows[i]["有效期"].ToString(), out date))
                {
                    updateClumns += string.Format("vldat=to_date('{0}','yyyy-MM-dd')",Convert.ToDateTime(dt.Rows[i]["有效期"]).ToString("yyyy-MM-dd")) + ",";
                }
            int day = 0;
            if (dt.Columns.Contains("送检周期") && int.TryParse(dt.Rows[i]["送检周期"].ToString(),out day))
                {
                    updateClumns += string.Format("sendCheckCycle='{0}'",dt.Rows[i]["送检周期"]) + ",";
                }
            if (!string.IsNullOrWhiteSpace(updateClumns))
            {
                arrSQL.Add(string.Format(updateStr, updateClumns.TrimEnd(','), dt.Rows[i]["批次号"], dt.Rows[i]["物料编码"]));
            }
        }
        if (arrSQL.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
            result.Message = (result.Result ? "导入成功" : "导入失败") + (string.IsNullOrWhiteSpace(msg) ? "" : "：材料库存台账不存在(物料编码-批次号)【" + msg.TrimEnd(',') + "】的信息。");
        }
        else
        {
            result.Message = "导入模板数据为空或未包含【有效期、送检周期】列或材料编码对应批次在台账中不存在，请检查数据再进行操作";
        }
        return result;
    }
    #endregion
    
     

    /// <summary>
    /// 订单查询
    /// </summary>
    /// <param name="context"></param>
    private void GetMfgOrderData(HttpContext context)
    {
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        var dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        var para = new Dictionary<string,string>();
        para.Add("PageSize", pageSize);
        para.Add("CurrentPageIndex", pageNumber);
        para.Add("FactoryID", m_UserInfo.FactoryID);
        para.Add("ZS", "ZS");
        para.Add("OrderTypeName", "PD05,PD06,PD09,PD11,PD12,PD13,PD14");
        if (dic.ContainsKey("OrderCloseStatus") && !string.IsNullOrWhiteSpace(dic["OrderCloseStatus"])) { para.Add("OrderCloseStatus", dic["OrderCloseStatus"]); } else { para.Add("OrderCloseStatus","0"); };
        if (!string.IsNullOrWhiteSpace(dic["Machine"])) { para.Add("Machine", dic["Machine"]); }
        if (!string.IsNullOrWhiteSpace(dic["mfgOrderName"])) { para.Add("mfgOrderName", dic["mfgOrderName"]); }
        if (!string.IsNullOrWhiteSpace(dic["mfgOrderNameLast"])) { para.Add("mfgOrderNameLast", dic["mfgOrderNameLast"]); }
        if (!string.IsNullOrWhiteSpace(dic["OrderTypeIndex"])) { para.Add("OrderTypeIndex", dic["OrderTypeIndex"]); }
        if (!string.IsNullOrWhiteSpace(dic["OrderStatus"])) { para.Add("OrderStatus", dic["OrderStatus"]); }
        var result = m_MfgOrderObj.GetMfgOrderData(para);
        result.DBTable = FillMfgDatatable(result.DBTable, dic["OrderStatus"]);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    public DataTable FillMfgDatatable(DataTable dt, string orderstatus) 
    {
        var strsql = @"  SELECT 
                    p.productid,wb.workflowname,wf.workflowrevision,wf.workflowid,
                    pf.productfamilyid,pf.productfamilyname
                    FROM product p
                    LEFT JOIN productworkflowmapping pw ON p.productid=pw.productid
                    LEFT JOIN workflow wf ON wf.workflowid=pw.workflowid
                    LEFT JOIN workflowbase wb ON wb.workflowbaseid=wf.workflowbaseid
                    LEFT JOIN workflowproductfamilylist wff ON wff.workflowid=wf.workflowid
                    LEFT JOIN productfamily pf ON pf.productfamilyid=wff.productfamilylistid 
                    WHERE wf.phasenode IS NULL AND p.productid='{0}' 
                    order by wf.workflowid desc";
        var strsqlEng = @"SELECT ep.productid,wb.workflowname,wf.workflowrevision,wf.workflowid,
                        pf.productfamilyid,pf.productfamilyname 
                from enginepartplan ep 
                LEFT JOIN workflowstep ws ON ws.workflowstepid=ep.workflowstepid
                LEFT JOIN workflow wf ON wf.workflowid=ws.workflowid
                LEFT JOIN workflowbase wb ON wb.workflowbaseid=wf.workflowbaseid
                LEFT JOIN workflowproductfamilylist wff ON wff.workflowid=wf.workflowid
                LEFT JOIN productfamily pf ON pf.productfamilyid=wff.productfamilylistid
                where ep.mfgorderid='{0}' and ep.productid='{1}' AND ep.type='1' AND ROWNUM=1";
        
        foreach(DataRow dr in dt.Rows)
        {
            var mfgorderid = dr["mfgorderid"].ToString();
            var productid = dr["productid"].ToString();
            var dttemp = new DataTable();
            if(orderstatus.Equals("1")){
                 dttemp = DatabaseHelper.Query_ReturnDatTable(String.Format(strsql, productid));
            }else{
                 dttemp = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlEng, mfgorderid, productid));
                if (dttemp.Rows.Count == 0) {
                    dttemp = DatabaseHelper.Query_ReturnDatTable(String.Format(strsql, productid));
                }
            }
            if (dttemp.Rows.Count > 0)
            {
                dr["WORKFLOWID"] = dttemp.Rows[0]["WORKFLOWID"].ToString();
                dr["productfamilyname"] = dttemp.Rows[0]["productfamilyname"].ToString();
                dr["productfamilyid"] = dttemp.Rows[0]["productfamilyid"].ToString();
                dr["WORKFLOWNAME"] = dttemp.Rows[0]["WORKFLOWNAME"].ToString();
                dr["WORKFLOWREVISION"] = dttemp.Rows[0]["WORKFLOWREVISION"].ToString();
            }
        }
        return dt;
    }
    /// <summary>
    /// 整台发动机物料信息
    /// </summary>
    /// <param name="context"></param>
    private void GetMaterialListData(HttpContext context)
    {
        var dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        var para = new Dictionary<string, string>();
        para.Add("ProductId", dic["PRODUCTID"]);
        para.Add("ProductName", dic["PRODUCTNAME"]);
        para.Add("MfgOrderId", dic["MFGORDERID"]);
        para.Add("ProductFamilyid", dic["PRODUCTFAMILYID"]);
        para.Add("StartDate",Convert.ToDateTime(dic["FACTORYSTARDATE"].ToString()).ToString("yyyy-MM-dd"));
        para.Add("EngineNo", dic["ENGINENO"]);
        para.Add("workflowId", dic["WORKFLOWID"]);
        var ds = m_PartPlanObj.SaveMfgOrderSession(para);
        var workflowIds = string.Empty;
        foreach (DataRow row in ds.Tables["dtMerge"].Rows) 
        {
            if (!workflowIds.Contains(row["WORKFLOWID"].ToString()))
            {
                workflowIds += "'" + row["WORKFLOWID"] + "',";
            }
        }
        //add by longb 订单件号名称判断初装检装包含（Z1:初装，Z2:检装）
        var columns = "pm.Qtyrequired";
        if (dic["PRODUCTNAME"].Contains("Z2")) {
            columns = "pm.QTY2REQUIRED";
        }
        var selectSQL = @" SELECT  p.productid childproductid,
                        pb.productname PRODUCTNAME,p.description PRODUCTDESC,pm.BIGSMALLPART ,
                        case when c2.ERPTOTALQTY>SUM(nvl({2},0)) then SUM(nvl({2},0))-nvl(c3.CONFIRMQTY,0) else nvl(c2.ERPTOTALQTY,0)-nvl(c3.CONFIRMQTY,0) end QTY,
                         SUM(nvl({2},0))NEEDQTY
                        ,c.ERPQTY,c.PRODUCTBATCHNO,c2.ERPTOTALQTY,nvl(c3.CONFIRMQTY,0) CONFIRMQTY                         
                        FROM bom b
                        LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid
                        LEFT JOIN productmateriallistitem pm ON b.bomid=pm.bomid
                        LEFT JOIN product p ON pm.productid=p.productid
                        LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                        inner JOIN productworkflowmapping pw ON b.workflowid=pw.workflowid
                        LEFT JOIN (
                             SELECT SUM(c.QTY) ERPQTY,c.PRODUCTBATCHNO,c.PRODUCTNAME FROM COLLECTERPMATERIAL c 
                            WHERE c.MFGORDERNAME ='{0}'
                            GROUP BY c.PRODUCTBATCHNO,c.PRODUCTNAME 
                        ) c ON c.PRODUCTNAME=pb.productname
                        LEFT JOIN (
                              SELECT SUM(c.QTY) ERPTOTALQTY,c.PRODUCTNAME FROM COLLECTERPMATERIAL c 
                            WHERE c.MFGORDERNAME ='{0}'
                            GROUP BY c.PRODUCTNAME 
                        ) c2 ON c2.PRODUCTNAME=pb.productname 
                        LEFT JOIN (
                            SELECT SUM(coll.CONFIRMQTY) CONFIRMQTY,coll.COLLECTERPMATERIALID productname
                            FROM collecterpmaterialconfirm coll 
                            LEFT JOIN MFGORDER m ON m.MFGORDERID =coll.CONTAINERID 
						    WHERE m.MFGORDERNAME ='{0}' GROUP BY coll.COLLECTERPMATERIALID
                        )  c3 ON c3.PRODUCTNAME=pb.productname                                                                
                        WHERE --pb.productname like '%BSP%' AND 
                            b.workflowid IN({1}) 
                        GROUP BY  p.productid ,pb.productname,p.description,pm.BIGSMALLPART,c.ERPQTY,c.PRODUCTBATCHNO,c2.ERPTOTALQTY,c3.CONFIRMQTY   
                        ORDER BY pb.productname ASC";
        var paraPage=new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(context.Request["pageNumber"].ToString());
        paraPage.PageSize = Convert.ToInt32(context.Request["PageSize"].ToString());
        paraPage.strSQL = string.Format(selectSQL, dic["MFGORDERNAME"], workflowIds.TrimEnd(','), columns);
        var result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 整台发动机物料（零件）--收料确认
    /// </summary>
    /// <param name="context"></param>
    private void ReciveMaterialListCofirm(HttpContext context) 
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var mfgorderid = context.Request["mfgorderid"].ToString();
        var insertSQL = @"INSERT INTO collecterpmaterialconfirm(COLLECTERPMATERIALCONFIRMID,COLLECTERPMATERIALID,CONFIRMDATE,CONFIRMEMPOYEEID,CONFIRMQTY,CONTAINERID)
                        values(sys_guid(),'{0}',sysdate,'{1}','{2}','{3}')";
        var arrList = new ArrayList();
        foreach(DataRow row in dt.Rows)
        {
            if (Convert.ToDecimal(row["QTY"].ToString()) > 0) {
                arrList.Add(string.Format(insertSQL, row["PRODUCTNAME"], m_UserInfo.EmployeeID.ToString(), row["QTY"], mfgorderid)); 
            }
        }
        var result = new uMESProcessResult();
        if (arrList.Count>0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
            result.Message = result.Result ? "确认成功" : "确认失败";
        }else{
            result.Message="本次收料数量未有大于0的记录";
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
}